package com.neuedu.javase.jdbc.dao;

import com.neuedu.javase.jdbc.entity.User;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @auth 金山老师
 * title:   书籍代码
 * creatime: 2022-10-11 8:51
 * 描述 : CRUD
 */
public class UserDao extends BaseDao {

    //查询

    /**
     * 根据主键查询一个
     */
    public User selectById(Integer id){
        User user = null;
        Connection conn  = null;
        Statement stmt  = null;
        try {
            conn = super.getConnection();
            stmt = conn.createStatement();

            String sql = "select * from user where id = "+id;
            ResultSet rs = stmt.executeQuery(sql);

            if(rs.next()){
                user = new User();
//                int id = rs.getInt("id");
                String uname = rs.getString("username");
                String displayname = rs.getString("displayname");
                Date birthday = rs.getDate("birthday");
                String password = rs.getString("password");
                String gender = rs.getString("gender");
                String telephone = rs.getString("telephone");
                String email = rs.getString("email");
                //lastLoginTime
                Date lastLoginTime = rs.getTimestamp("last_logintime");
                Date createTime = rs.getTimestamp("create_time");
                String valid = rs.getString("valid");
                user.setId(id);
                user.setUsername(uname);
                user.setDisplayname(displayname);
                user.setBirthday(birthday);
                user.setGender(gender);
                user.setTelephone(telephone);
                user.setPassword(password);
                user.setEmail(email);
                user.setLastLogintime(lastLoginTime);
                user.setCreate_time(createTime);
                user.setValid(valid);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(conn,stmt,null);
        }


        return user;
    }

    /**
     * 根据条件查询多个数据  集合
     */
    public List<User> selectList(String condation ,Object ... params){
        List<User> list = new ArrayList();
        Connection conn  = null;
        PreparedStatement stmt  = null;  //预编译的Statement
        try {
            conn = super.getConnection();
//            stmt = conn.createStatement();旧的
  
            String sql = "select * from user " +(condation == null?"":condation);
            System.out.println("sql = " + sql);

            stmt = conn.prepareStatement(sql); //  新的类型 ? ? ? ?

    
            //绑定变量
            int index = 0;
            for (int i = 0; i < params.length; i++) {
//                stmt.setString(1);
//                stmt.setInt(2);
                stmt.setObject(++index ,params[i]);
//                System.out.println("setObject("+index+") = " + params[i]);
            }

            ResultSet rs = stmt.executeQuery();
            stmt.executeQuery();

            User user = null;
            while (rs.next()){
                user = new User();
                int id = rs.getInt("id");
                String uname = rs.getString("username");
                String displayname = rs.getString("displayname");
                Date birthday = rs.getDate("birthday");
                String password = rs.getString("password");
                String gender = rs.getString("gender");
                String telephone = rs.getString("telephone");
                String email = rs.getString("email");
                //lastLoginTime
                Date lastLoginTime = rs.getTimestamp("last_logintime");
                Date createTime = rs.getTimestamp("create_time");
                String valid = rs.getString("valid");
                user.setId(id);
                user.setUsername(uname);
                user.setDisplayname(displayname);
                user.setBirthday(birthday);
                user.setGender(gender);
                user.setTelephone(telephone);
                user.setPassword(password);
                user.setEmail(email);
                user.setLastLogintime(lastLoginTime);
                user.setCreate_time(createTime);
                user.setValid(valid);

                list.add(user);

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(conn,stmt,null);
        }


        return list;
    }


    /**
     * 分页的查询
     * @param condation
     * @param current
     * @param pageSize
     * @return
     */
    public List<User> selectList(String condation ,int current ,int pageSize){
        List<User> list = new ArrayList();
        Connection conn  = null;
        Statement stmt  = null;
        try {
            conn = super.getConnection();
            stmt = conn.createStatement();

            int start = (current-1)*pageSize;

            String sql = "SELECT * FROM (   select * from user " +(condation == null?"":condation)+" ) t LIMIT  "+start+" ,"+pageSize ;
            System.out.println("sql = " + sql);
            ResultSet rs = stmt.executeQuery(sql);

            User user = null;
            while (rs.next()){
                user = new User();
                int id = rs.getInt("id");
                String uname = rs.getString("username");
                String displayname = rs.getString("displayname");
                Date birthday = rs.getDate("birthday");
                String password = rs.getString("password");
                String gender = rs.getString("gender");
                String telephone = rs.getString("telephone");
                String email = rs.getString("email");
                //lastLoginTime
                Date lastLoginTime = rs.getTimestamp("last_logintime");
                Date createTime = rs.getTimestamp("create_time");
                String valid = rs.getString("valid");
                user.setId(id);
                user.setUsername(uname);
                user.setDisplayname(displayname);
                user.setBirthday(birthday);
                user.setGender(gender);
                user.setTelephone(telephone);
                user.setPassword(password);
                user.setEmail(email);
                user.setLastLogintime(lastLoginTime);
                user.setCreate_time(createTime);
                user.setValid(valid);

                list.add(user);

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(conn,stmt,null);
        }


        return list;
    }
    
    /**
     * 查询总条数
     */
    public long selectCount(String condation){
        long count = 0;
        Connection conn  = null;
        Statement stmt  = null;
        try {
            conn = super.getConnection();
            stmt = conn.createStatement();

            String sql = "select count(1) count from user " +(condation == null?"":condation);
            ResultSet rs = stmt.executeQuery(sql);


            if (rs.next()){
//                count = rs.getLong(1);
                count = rs.getLong("count");

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(conn,stmt,null);
        }

        return count;
    }

    //更新 根据主键更新 返回更新成功的条数
    /**
     * 通过User实体类传递数据
     *
     * @param user
     * @return
     */
    public int updateById(User user) {
        Connection con = null;
        Statement stmt = null;
        int count = 0;
        try {

            con = super.getConnection();

            stmt = con.createStatement();
            SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String strBirthDay = sdf1.format(user.getLastLogintime());
            String strLastLogin = sdf2.format(user.getLastLogintime());
            StringBuilder sql = new StringBuilder();
            sql.append(" UPDATE 									");
            sql.append("   `user`                                 "  );
            sql.append(" SET                                      "  );
            sql.append("                                          "  );
            sql.append("   `username` = '"+user.getUsername()+"',               "  );
            sql.append("   `displayname` = '"+user.getDisplayname()+"',         "  );
            sql.append("   `password` = '"+user.getPassword()+"',               "  );
            sql.append("   `birthday` = '"+strBirthDay+"',               "  );
            sql.append("   `gender` = '"+user.getGender()+"',                   "  );
            sql.append("   `telephone` = '"+user.getTelephone()+"',             "  );
            sql.append("   `email` = '"+user.getEmail()+"',                     "  );
            sql.append("   `last_logintime` = '"+strLastLogin+"',   "  );
            sql.append("   `valid` = '"+user.getValid()+"'                      "  );
            sql.append(" WHERE `id` = "+user.getId()+"                       "  );


            System.out.println("sql = " + sql.toString());
            count = stmt.executeUpdate(sql.toString());

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.close(con, stmt);
        }

        return count;
    }

    //删除 根据主键删除，返回删除的函数
    public int deleteById(Integer id) {
        Connection con = null;
        Statement stmt = null;
        int count = 0;
        try {

            con = super.getConnection();

            stmt = con.createStatement();

            String sql = " delete from user where id =  " + id;
            System.out.println("sql = " + sql);
            count = stmt.executeUpdate(sql);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.close(con, stmt);
        }

        return count;
    }


    //插入  拿到数据插入，biang返回影响的行数
    public int insert(Map map) {
        Connection con = null;
        Statement stmt = null;
        int count = 0;
        try {

            con = super.getConnection();

            stmt = con.createStatement();

            String sql = " insert  into `user`(`username`,`displayname`,`password`,`birthday`,`gender`,`telephone`,`email`) " +
                    "values ('" + map.get("username") + "','" + map.get("displayname") + "'," +
                    "'" + map.get("password") + "','" + map.get("birthday") + "','" + map.get("gender") + "','" + map.get("telephone") + "','" + map.get("email") + "') ";
            System.out.println("sql = " + sql);
            count = stmt.executeUpdate(sql);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.close(con, stmt);
        }

        return count;
    }


    /**
     * 通过User实体类传递数据
     *
     * @param user
     * @return
     */
    public int insert(User user) {
        Connection con = null;
        Statement stmt = null;
        int count = 0;
        try {

            con = super.getConnection();

            stmt = con.createStatement();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String sql = " insert  into `user`(`username`,`displayname`,`password`,`birthday`,`gender`,`telephone`,`email`) " +
                    "values ('" + user.getUsername() + "','" + user.getDisplayname() + "'," +
                    "'" + user.getPassword() + "','" + sdf.format(user.getBirthday()) + "','" + user.getGender() + "','" + user.getTelephone() + "','" + user.getEmail() + "') ";
            System.out.println("sql = " + sql);
            count = stmt.executeUpdate(sql);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.close(con, stmt);
        }

        return count;
    }


}
